﻿using Common.Helpers;
using DataAccessLayer.DatabaseOperations;
using DataAccessLayer.Helper;
using MasterEntities.Abstraction;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLayer
{
    public class GenericDataAccessLayer<T> : IGenericDataAccessLayer<T> where T : class
    {

        public static readonly string connectionString = string.Format(ConfigurationManager.ConnectionStrings["ADO.NET"].ConnectionString, GlobalVariables.Instance.dbConnection);
        private static SqlConnection m_SqlConnection;
        private SqlTransaction m_Transaction;

        public GenericDataAccessLayer()
        {
            m_SqlConnection = SqlConnectionInstance;
        }

        public static SqlConnection SqlConnectionInstance
        {
            get
            {
                if (m_SqlConnection == null)
                    m_SqlConnection = new SqlConnection(connectionString);
                return m_SqlConnection;
            }
        }

        private static GenericDataAccessLayer<T> m_Instance;
        public static GenericDataAccessLayer<T> Instance
        {
            get
            {
                if (m_Instance == null)
                    m_Instance = new GenericDataAccessLayer<T>();
                return m_Instance;
            }
        }

        public void OpenConnection()
        {
            m_SqlConnection.Open();
        }

        public void CloseConnection()
        {
            m_SqlConnection.Close();
        }


        DataAccessLayer.Helper.ICommandTextGenerator gen = null;

        protected bool ExecuteCUDOperation(IDomainObject obj, AbstractExecutionTemplate template)
        {
            string helpComm = string.Empty;
            bool transactionResult = false;
            SqlTransaction sqlTran = null;
            SqlConnection conn = null;
            try
            {
                using (conn = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand())
                    {
                        conn.Open();
                        command.Connection = conn;

                        sqlTran = conn.BeginTransaction();
                        command.Transaction = sqlTran;

                        gen = new DataAccessLayer.Helper.CommandTextGenerator(template, obj);

                        Dictionary<int, string> dict = gen.GetCommandText(obj);

                        if (dict != null && dict.Count() > 0)
                            foreach (var item in dict.OrderBy(d => d.Key))
                            {
                                if(item.Value.Contains("@Slika"))
                                    command.Parameters.AddWithValue("@Slika", System.Data.SqlDbType.Image).Value = (obj.GetProfilePicture() == null ? new byte[0] : obj.GetProfilePicture());
                                helpComm = item.Value;
                                transactionResult = ExecuteCommandNonQuery(command, item.Value);
                            }

                        sqlTran.Commit();
                    }
                }
                return transactionResult;
            }
            catch (Exception ex)
            {
                sqlTran.Rollback();
                Console.WriteLine(helpComm);
                return transactionResult;
            }
            finally
            {
                if (conn.State == System.Data.ConnectionState.Open)
                    conn.Close();
            }
        }

        public List<T> GetAll(params Expression<Func<T, object>>[] navigationProperties)
        {
            return ReadData<T>(string.Empty, navigationProperties);
        }

        private List<T> ReadData<T>(string where, params Expression<Func<T, object>>[] navigationProperties)
        {
            List<T> resultingList = null;
            using (m_SqlConnection)
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    Type obj = typeof(T) as Type;
                    comm.Connection = m_SqlConnection;

                    SelectCommand sc = new SelectCommand(obj);
                    comm.CommandText = sc.GenerateCommand(where);
                    if (m_SqlConnection.State != System.Data.ConnectionState.Open)
                    {
                        m_SqlConnection.ConnectionString = connectionString;
                        m_SqlConnection.Open();
                    }

                    using (SqlDataReader reader = comm.ExecuteReader())
                    {
                        resultingList = new List<T>();
                        PropertyInfo[] propInfo = obj.GetProperties().Where(p => p.PropertyType.Namespace != "System.Collections.Generic").Select(s => s).ToArray();
                        object ob = null;
                        long key = 0;
                        List<object> listOfValues = null;
                        List<Type> listOfPropTypes = new List<Type>();

                        foreach (PropertyInfo propInf in propInfo)
                        {
                            listOfPropTypes.Add(propInf.PropertyType);
                        }

                        while (reader.Read())
                        {
                            listOfValues = new List<object>();

                            foreach (PropertyInfo pi in propInfo)
                            {
                                if (pi.PropertyType.Namespace != "MasterEntities")
                                {
                                    ob = reader[pi.Name];

                                    if (ob == DBNull.Value)
                                        ob = null;

                                    listOfValues.Add(ob);
                                }
                                else
                                {
                                    long? rr = reader[pi.Name + "Id"] as long?;

                                    if (rr != null && rr.HasValue && rr.Value != 0)
                                    {
                                        key = (long)rr;

                                        listOfValues.Add(LoadReference(pi, key));
                                    }
                                    else
                                    {
                                        listOfValues.Add(null);
                                    }

                                }
                                ////////
                            }

                            ConstructorInfo ci = obj.GetConstructor(listOfPropTypes.ToArray());
                            T typ = (T)Convert.ChangeType(ci.Invoke(listOfValues.ToArray()), typeof(T));
                            resultingList.Add(typ);
                        }
                        m_SqlConnection.Close();
                    }
                }
            }
            return resultingList;
        }

        private object LoadReference(PropertyInfo pi, long key)
        {
            object obToReturn = null;
            long innerKey = 0;
            List<object> identiff = new List<object>();

            string command = string.Empty;
            IDomainObject dom = (IDomainObject)Activator.CreateInstance(pi.PropertyType);

            SelectCommand select = new SelectCommand(dom.GetType());
            string help = string.IsNullOrEmpty(dom.BaseClassName()) || GlobalVariables.Instance.dbConnection != Common.Helpers.Enums.DatabaseConnection.MasterApp1 ? "Id=" : "fi.Id=";
            string where = string.Format("{0}{1}", help, key.ToString());
            if (dom != null && key != 0)
                command = select.GenerateCommand(where);//string.Format("SELECT * FROM {0}.{1} WHERE Id={2}", dom.SchemaName(), dom.GetTableName(), key.ToString());

            if (!string.IsNullOrEmpty(command))
            {
                using (SqlCommand newCommand = new SqlCommand())
                {
                    
                    SqlConnection connection = new SqlConnection(connectionString);
                    newCommand.Connection = connection;
                    newCommand.CommandText = command;
                    if (connection.State != System.Data.ConnectionState.Open)
                    {
                        connection.ConnectionString = connectionString;
                        connection.Open();
                    }

                    PropertyInfo[] propI = dom.GetType().GetProperties().Where(p => p.PropertyType.Namespace != "MasterEntities" && p.PropertyType.Namespace != "System.Collections.Generic").Select(s => s).ToArray();

                    using (SqlDataReader read = newCommand.ExecuteReader())
                    {
                        object ob = null;
                        List<Type> listtt = new List<Type>();

                        foreach (PropertyInfo propInf in propI)
                        {
                            if (propInf.PropertyType.Namespace != "MasterEntities")
                                listtt.Add(propInf.PropertyType);
                        }
                        while (read.Read())
                        {
                            foreach (PropertyInfo piii in propI)
                            {
                                ob = read[piii.Name];

                                //if (piii.PropertyType.Namespace != "MasterEntities")
                                //{
                                //    long? rr = read[pi.Name + "IzdavanjaId"] as long?;

                                //    if (rr != null && rr.HasValue && rr.Value != 0)
                                //    {
                                //        innerKey = (long)rr;

                                //        identiff.Add(LoadReference(pi, innerKey));
                                //    }
                                //}

                                if (ob == DBNull.Value)
                                    ob = null;

                                //if (ob != null)
                                identiff.Add(ob);
                            }
                        }
                        ConstructorInfo constructor = dom.GetType().GetConstructor(listtt.ToArray());
                        //Type ttt = dom.GetType();
                        obToReturn = (IDomainObject)Convert.ChangeType(constructor.Invoke(identiff.ToArray()), dom.GetType());
                        //listOfValues.Add(typp);
                    }
                    connection.Close();
                }                
            }
            return obToReturn;
        }


        public List<T> GetList(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties)
        {
            return ReadData<T>(string.Empty).Where(where).ToList();
        }

        public T GetEntity(Func<T, bool> where, params Expression<Func<T, object>>[] navigationProperties)
        {
            return ReadData<T>(string.Empty).Where(where).FirstOrDefault<T>();
        }

        private static Expression<Func<T, bool>> FuncToExpression(Func<T, bool> f)
        {
            return x => f(x);
        }

        private bool ExecuteCommandNonQuery(SqlCommand command, string commandText)
        {
            command.CommandText = commandText;

            if (command.ExecuteNonQuery() != 1)
                return false;

            return true;
        }

        public bool Create(IDomainObject obj, AbstractExecutionTemplate template)
        {
            return ExecuteCUDOperation(obj, template);
        }

        public bool Update(IDomainObject obj, AbstractExecutionTemplate template)
        {
            return ExecuteCUDOperation(obj, template);
        }

        public bool Delete(IDomainObject obj, AbstractExecutionTemplate template)
        {
            return ExecuteCUDOperation(obj, template);
        }

    }
}
